﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CMP.ERP.Model;
using CMP.ERP.Helper;
using System.Data;

namespace CMP.ERP.DAL
{
    public  class ClientMessageDAL
    {
        /// <summary>
        /// 实例化SQLHelper对象
        /// </summary>
        SQLHelper p = new SQLHelper();

        /// <summary>
        /// 显示客户管理信息
        /// </summary>
        /// <returns></returns>
        public DataTable ClientMessageAll()
        {
            string sql = "select Client_Id,Client_Name,Client_Phone,Client_Address,Client_Email,Client_Remark,Province.P_Id,Province.P_Name from ClientMessage join Province on ClientMessage.P_Id=Province.P_Id";
            return p.ExecDataTable(sql);
        }

        /// <summary>
        ///1、删除客户管理信息
        /// </summary>
        /// <param name="HB_Id"></param>
        /// <returns></returns>
        public int ClientMessageDelete(int id)
        {
            int i = p.ExecNonquery(string.Format("delete from ClientMessage where Client_Id={0}", id));
            return i;
        }
       
        /// <summary>
        /// 2、批量删除客户管理信息
        /// </summary>
        /// <param name="Client_Id"></param>
        /// <returns></returns>
        public bool ClientMessageDeleteAll(string Client_Id)
        {
            int i = p.ExecNonquery(string.Format("delete from ClientMessage where Client_Id in ({0})", Client_Id));
            return i>0;
        }
        /// <summary>
        /// 添加客户管理信息
        /// </summary>
        /// <param name="h"></param>
        /// <returns></returns>
        public int AddClientMessage(ClientMessage c)
        {
            int i = p.ExecNonquery(string.Format("insert into ClientMessage(Client_Name,Client_Phone,Client_Address,Client_Email,Client_Remark,P_Id) values('{0}','{1}','{2}','{3}','{4}',{5})", c.Client_Name, c.Client_Phone, c.Client_Address, c.Client_Email, c.Client_Remark, c.P_Id));
            return i;
        }

        /// <summary>
        /// 根据客户编号找到客户信息
        /// </summary>
        /// <param name="Client_Id"></param>
        /// <returns></returns>
        public DataTable GetClientMessage(int id)
        {
            string sql = string.Format("select * from ClientMessage WHERE Client_Id ='{0}'", id);
            return p.ExecDataTable(sql);
        }

        /// <summary>
        /// 修改客户管理信息
        /// </summary>
        /// <param name="client"></param>
        /// <returns></returns>
        public int UptClientMessage(ClientMessage client)
        {
            string sql = string.Format(" update ClientMessage set Client_Name='{0}',Client_Phone='{1}',Client_Address='{2}',Client_Email='{3}',Client_Remark='{4}',P_Id={5} where Client_Id='{6}'", client.Client_Name, client.Client_Phone, client.Client_Address, client.Client_Email, client.Client_Remark, client.P_Id, client.Client_Id);
            return p.ExecNonquery(sql);
        }

        /// <summary>
        /// 根据客户名称进行查询
        /// </summary>
        /// <param name="HB_Name"></param>
        /// <returns></returns>
        public DataTable SearchClientMessage(string Client_Name)
        {
            string sql = string.Format("select * from ClientMessage where Client_Name  like '%{0}%'", Client_Name);
            return p.ExecDataTable(sql);
        }
    }
}
